1 Imports System.Data.SqlClient
2 Imports Excel = Microsoft.Office.Interop.Excel
3
4 Public Class frmSalesReport
5
6 Dim a, b, c, d As Decimal
7 Sub Reset()
8 dtpDateFrom.Text = Today
9 dtpDateTo.Text = Today
10 End Sub
11 Private Sub btnReset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReset.Click
12 Reset()
13 End Sub
14
15
16 Private Sub btnClose_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
17 Me.Close()
18 End Sub
19
20 Private Sub Timer1_Tick(sender As System.Object, e As System.EventArgs) Handles Timer1.Tick
21 Cursor = Cursors.Default
22 Timer1.Enabled = False
23 End Sub
24
25
26 Private Sub btnGetData_Click(sender As System.Object, e As System.EventArgs) Handles btnGetData.Click
27 Try
28 con = New SqlConnection(cs)
29 con.Open()
30 Dim ctn As String = "select InvoiceNo from InvoiceInfo where InvoiceDate between @d1 and @d2"
31 cmd = New SqlCommand(ctn)
32 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
33 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value.Date
34 cmd.Connection = con
35 rdr = cmd.ExecuteReader()
36
37 If Not rdr.Read() Then
38 MessageBox.Show("Sorry..No record found", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
39 If (rdr IsNot Nothing) Then
40 rdr.Close()
41 End If
42 Return
43 End If
44 Cursor = Cursors.WaitCursor
45 Timer1.Enabled = True
46 Dim rpt As New rptSales2 'The report you created.
47 Dim myConnection As SqlConnection
48 Dim MyCommand, MyCommand1 As New SqlCommand()
49 Dim myDA, myDA1 As New SqlDataAdapter()
50 Dim myDS As New DataSet 'The DataSet you created.
51 myConnection = New SqlConnection(cs)
52 MyCommand.Connection = myConnection
53 MyCommand1.Connection = myConnection
54 MyCommand.CommandText = "SELECT * FROM InvoiceInfo INNER JOIN Customer ON InvoiceInfo.CustomerID = Customer.ID where InvoiceDate between @d1 and @d2 order by InvoiceDate"
55 MyCommand.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
56 MyCommand.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value.Date
57 MyCommand1.CommandText = "SELECT * from Company"
58 MyCommand.CommandType = CommandType.Text
59 MyCommand1.CommandType = CommandType.Text
60 myDA.SelectCommand = MyCommand
61 myDA1.SelectCommand = MyCommand1
62 myDA.Fill(myDS, "InvoiceInfo")
63 myDA.Fill(myDS, "Customer")
64 myDA1.Fill(myDS, "Company")
65 con = New SqlConnection(cs)
66 con.Open()
67 cmd = New SqlCommand("SELECT CONVERT(varchar(10),YEAR(InvoiceDate)) AS Year, SUM(GrandTotal) AS GrandTotal FROM InvoiceInfo where InvoiceDate between @d3 and @d4 GROUP BY YEAR(InvoiceDate) ORDER BY Year", con)
68 cmd.Parameters.Add("@d3", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
69 cmd.Parameters.Add("@d4", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value.Date
70 adp = New SqlDataAdapter(cmd)
71 dtable = New DataTable()
72 adp.Fill(dtable)
73 con.Close()
74 myDS.Tables.Add(dtable)
75 myDS.WriteXmlSchema("TotalSales.xml")
76 rpt.Subreports(0).SetDataSource(myDS)
77 rpt.Subreports(1).SetDataSource(myDS)
78 rpt.SetParameterValue("p1", dtpDateFrom.Value.Date)
79 rpt.SetParameterValue("p2", dtpDateTo.Value.Date)
80 rpt.SetParameterValue("p7", Today)
81 frmReport.CrystalReportViewer1.ReportSource = rpt
82 frmReport.ShowDialog()
83 Catch ex As Exception
84 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
85 End Try
86 End Sub
87
88 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
89 Try
90 con = New SqlConnection(cs)
91 con.Open()
92 Dim ctn As String = "select InvoiceNo from InvoiceInfo where InvoiceDate between @d1 and @d2"
93 cmd = New SqlCommand(ctn)
94 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
95 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value.Date
96 cmd.Connection = con
97 rdr = cmd.ExecuteReader()
98
99 If Not rdr.Read() Then
100 MessageBox.Show("Sorry..No record found", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
101 If (rdr IsNot Nothing) Then
102 rdr.Close()
103 End If
104 Return
105 End If
106 Cursor = Cursors.WaitCursor
107 Timer1.Enabled = True
108 Dim rpt As New rptSales1 'The report you created.
109 Dim myConnection As SqlConnection
110 Dim MyCommand, MyCommand1 As New SqlCommand()
111 Dim myDA, myDA1 As New SqlDataAdapter()
112 Dim myDS As New DataSet 'The DataSet you created.
113 myConnection = New SqlConnection(cs)
114 MyCommand.Connection = myConnection
115 MyCommand1.Connection = myConnection
116 MyCommand.CommandText = "SELECT Customer.ID, Customer.Name, Customer.Gender, Customer.Address, Customer.City, Customer.State, Customer.ZipCode, Customer.ContactNo, Customer.EmailID, Customer.Remarks,Customer.Photo, InvoiceInfo.Inv_ID, InvoiceInfo.InvoiceNo, InvoiceInfo.InvoiceDate, InvoiceInfo.CustomerID , InvoiceInfo.GrandTotal, InvoiceInfo.TotalPaid, InvoiceInfo.Balance, Invoice_Product.IPo_ID, Invoice_Product.InvoiceID, Invoice_Product.ProductID, Invoice_Product.CostPrice, Invoice_Product.SellingPrice, Invoice_Product.Margin,Invoice_Product.Qty, Invoice_Product.Amount, Invoice_Product.DiscountPer, Invoice_Product.Discount, Invoice_Product.VATPer, Invoice_Product.VAT, Invoice_Product.TotalAmount, Product.PID,Product.ProductCode, Product.ProductName FROM Customer INNER JOIN InvoiceInfo ON Customer.ID = InvoiceInfo.CustomerID INNER JOIN Invoice_Product ON InvoiceInfo.Inv_ID = Invoice_Product.InvoiceID INNER JOIN Product ON Invoice_Product.ProductID = Product.PID where InvoiceDate between @d1 and @d2 order by InvoiceDate"
117 MyCommand.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
118 MyCommand.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value.Date
119 MyCommand1.CommandText = "SELECT * from Company"
120 MyCommand.CommandType = CommandType.Text
121 MyCommand1.CommandType = CommandType.Text
122 myDA.SelectCommand = MyCommand
123 myDA1.SelectCommand = MyCommand1
124 myDA.Fill(myDS, "InvoiceInfo")
125 myDA.Fill(myDS, "Invoice_Product")
126 myDA.Fill(myDS, "Customer")
127 myDA.Fill(myDS, "Product")
128 myDA1.Fill(myDS, "Company")
129 con = New SqlConnection(cs)
130 con.Open()
131 Dim ct As String = "select ISNULL(sum(GrandTotal),0),ISNULL(sum(TotalPaid),0),ISNULL(sum(Balance),0) from InvoiceInfo where InvoiceDate between @d1 and @d2"
132 cmd = New SqlCommand(ct)
133 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
134 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value.Date
135 cmd.Connection = con
136 rdr = cmd.ExecuteReader
137 If (rdr.Read()) Then
138 a = rdr.GetValue(0)
139 b = rdr.GetValue(1)
140 c = rdr.GetValue(2)
141
142 Else
143 a = 0
144 b = 0
145 c = 0
146 End If
147 con.Close()
148 con = New SqlConnection(cs)
149 con.Open()
150 Dim ct1 As String = "select ISNULL(sum(Margin),0) from InvoiceInfo,Invoice_Product where InvoiceInfo.Inv_ID=Invoice_Product.InvoiceID and InvoiceDate between @d1 and @d2"
151 cmd = New SqlCommand(ct1)
152 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
153 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value.Date
154 cmd.Connection = con
155 rdr = cmd.ExecuteReader
156 If (rdr.Read()) Then
157 d = rdr.GetValue(0)
158 Else
159 d = 0
160 End If
161 con.Close()
162 rpt.SetDataSource(myDS)
163 rpt.SetParameterValue("p1", dtpDateFrom.Value.Date)
164 rpt.SetParameterValue("p2", dtpDateTo.Value.Date)
165 rpt.SetParameterValue("p3", a)
166 rpt.SetParameterValue("p4", b)
167 rpt.SetParameterValue("p5", c)
168 rpt.SetParameterValue("p6", d)
169 rpt.SetParameterValue("p7", Today)
170 frmReport.CrystalReportViewer1.ReportSource = rpt
171 frmReport.ShowDialog()
172 Catch ex As Exception
173 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
174 End Try
175 End Sub
176
177 Private Sub frmSalesReport_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
178
179 End Sub
180 End Class
